（基础篇）什么是 SQL？

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 11k | 阅读时长 ≈ 16 分钟

什么是 SQL？

  SQL（Structured Query Language，结构化查询语言）是 MySQL 服务器能听懂的语言，共分为四大类：DDL、DQL、DML、DCL：

  • 数据定义语言（Data Definition Language）——CREATE、ALTER、DROP 等语句
  • 数据查询语言（Data Query Language）——SELECT、FROM、WHERE 及其组成语句
  • 数据操作语言（Data Manipulation Language ）——UPDATE、INSERT、DELETE 语句
  • 数据控制语言（Data Control Language ）——COMMIT、ROLLBACK、SET TRANSACTION

数据定义语言 DDL

  数据定义语言可以用来创建（CREATE）、更新（ALTER）或删除（DROP）数据库对象（如数据库、数据库、视图、索引等等）。

CREATE 创建数据库对象

  CREATE语句可以用来创建数据库、数据表，视图，索引：

  • 创建数据库：CREATE DATABASE 数据库名;
  • 创建数据表：有多种写法
    • CREATE TABLE 表名(列名 数据类型 约束,列名 数据类型 约束...)；
    • CREATE TABLE 表名(列名 数据类型 ,列名 数据类型 ...约束...)
  • 创建视图：CREATE VIEW 视图名 AS SELECT ...；
  • 创建索引：CREATE INDEX 索引名 ON 表名(列名(长度))

ALTER 更新数据库对象

  ALTER语句可以用来更新数据库、数据表，索引：

  • 更新数据库：ALTER 数据库名 RENAME TO 新库名
  • 更新数据表：ALTER语句可以对指定数据表的添加或删除一个列或索引，还能修改列名的数据类型操作，：
    • 添加列：ALTER 表名 ADD 列名 数据类型 约束
    • 修改列ALTER 表名 MODIFY 列名 数据类型 约束
    • 删除列ALTER 表名 DROP 列名
    • 修改表名：ALTER TABLE 表名 RENAME TO 新表名;
  • 更新索引：
    • 添加索引：ALTER 表名 ADD INDEX 索引名
    • 删除索引：ALTER 表名 DROP INDEX 索引名

DROP 删除数据库对象

  DROP语句可以用来删除数据库、数据表，视图，索引：

  • 删除数据库：DROP DATABASE [IF EXISTS] name
  • 删除数据表：DROP TABLE [IF EXISTS] 表名
  • 删除视图：DROP VIEW [IF EXISTS] 视图名
  • 删除索引：DROP INDEX [IF EXISTS] 索引名 ON 表名

数据查询语言 DQL

  数据查询语言用来查询数据库的数据，并且可以对其进行相关操作（如排序、去重、分组）
  最常使用的 SQL 语句为SELECT语句，其用于从一个或多个表中检索信息。
  为了使用SELECT检索表数据，必须至少给出两条信息：

  • 想选择什么
  • 从什么地方选择

SELECT 检索数据

  SELECT语句可以检索数据：

  • 检索单个列：SELECT prod_name FROM products
  • 检索多个列：SELECT prod_name,prod_price FROM products
  • 检索所有列：SELECT * FROM products
  • 检索不同的行（并去重）：SELECT DISTINCT vend_id FROM products
  • 限制结果：SELECT prod_name FROM products limit 5

WHERE 过滤数据

  在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。
  WHERE子句需要在表名（FROM子句）之后给出，过滤的关键字包括：

关键字 说明 示例
= 检查单个值 SELECT * FROM products WHERE prod_price = 8.0
<>或!= 不匹配检查 SELECT * FROM products WHERE vend_id != 1003
BETWEEN 范围值检查 SELECT * FROM products WHERE prod_price BETWEEN 5 AND 60
IS NULL 空值检查 SELECT * FROM products WHERE prod_price IS NULL
AND 与操作符 SELECT * FROM products WHERE vend_id = 1003 AND prod_price = 8.0
OR 或操作符 SELECT * FROM products WHERE vend_id = 1003 OR prod_price = 8.0
IN 范围值操作符 SELECT * FROM products WHERE prod_price IN (5,60)
NOT 否定操作符 SELECT * FROM products WHERE prod_price NOT IN (5,60)
LIKE 模糊查询通配符_或% SELECT * FROM products WHERE prod_name LIKE '%茶'
REGEXP 正则表达式 SELECT prod_name FROM products WHERE prod_price REGEXP '.000'

GROUP BY 分组数据

  SQL 聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。
  但若要返回每个供应商提供的产品数目怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供 10 个以上产品的供应商怎么办？
  这时候需要对数据分组。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

1
2
3
SELECT vend_id,COUNT(*) AS num_prods 
FROM products 
GROUP BY vend_id;

  上面的SELECT语句指定了两个列，vend_id为产品供应商的 ID ,num_prods为计算字段。
  GROUP BY子句指示 MySQL 按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次，具体分组过程如下图：

  查询结果从下表中可以看到，供应商1002提供 1 个商品，其他供应商提供 2 个商品：

1
2
3
4
5
6
7
8
9
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1000 |         2 |
|    1001 |         2 |
|    1002 |         1 |
|    1003 |         2 |
|    1004 |         2 |
+---------+-----------+

兼容性问题

  MySQL 5.7.5 后only_full_group_by成为sql_mode的默认选项之一，这将导致一些 SQL 语句失效。

  举个例子，user表结构存在三列（name、age、category），数据如下：

行数\列名 name age category
1 zs 11 1
2 ls 12 1
2 Ww 13 2

  此时若执行以下命令：

1
2
3
SELECT name, category
FROM user 
GROUP BY category

  将出现以下报错：

1
Error Message:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre.

  为什么会出现这个报错呢？
  原因在于：将数据分组后，对category为1的分组数据而言，MySQL 不知道显示结果name是取zs还是ls
  那么，如何解决该问题呢？

解决方案一

  使用函数ANY_VALUE处理报错的字段，比如：

1
2
3
SELECT ANY_VALUE(name), category
FROM user 
GROUP BY category

  这样的话 MySQL 将从分组后的数据中随机取name。

解决方案二

  在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY，配置此属性如下即可：

1
2
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

  这样也能让 MySQL 将从分组后的数据中随机取name。

HAVING 过滤分组

  除了能用GROUP BY分组数据外，MySQL 还允许过滤分组，规定包括哪些分组，排除哪些分组。
  过滤分组需要使用HAVING关键字，如：

1
2
3
4
SELECT vend_id,COUNT(*) AS num_prods 
FROM products 
GROUP BY vend_id
HAVING COUNT(*) >= 2;

  查询结果如下：

1
2
3
4
5
6
7
8
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1000 |         2 |
|    1001 |         2 |
|    1003 |         2 |
|    1004 |         2 |
+---------+-----------+

ORDER BY 排序数据

  检索出的数据并不是以纯粹的随机顺序显示的。
  若不排序，数据一般将以它在底层表中出现的顺序显示，通过使用ORDER BY字句可以排序检索出的数据：

  • 排序单列数据：SELECT prod_name FROM products ORDER BY prod_price
  • 排序多列数据：SELECT prod_id,prod_name FROM products ORDER BY prod_price,prod_id
  • 指定排序方向（默认ASC升序，DESC降序）：SELECT * FROM products ORDER BY prod_price DESC

LIMIT 限制结果

  SELECT 语句返回所有匹配的行，它们可能是指定表中的每个行。
  为了返回第一行或前几行，可使用 LIMIT 子句。
  下面举一个例子:

1
2
3
SELECT prod_name
FROM products
LIMIT 5;

  此语句使用 SELECT 语句检索单个列。LIMIT 5 指示 MySQL 返回不多于 5 行。
  此语句的输出如下所示:

1
2
3
4
5
6
7
8
9
+-------------+
| prod_name   |
+-------------+
| 荣耀9999X   |
| MAC6666     |
| iphone6s    |
| S8 edge     |
| MIX 3       |
+-------------+

  为得出下一个 5 行，可指定要检索的开始行和行数：

1
2
3
SELECT prod_name
FROM products
LIMIT 5,5;

1
2
3
4
5
6
7
8
9
+--------------+
| prod_name    |
+--------------+
| NFC          |
| cloud server |
| sony camera  |
| ihpone XR    |
| Xperia XZ4   |
+--------------+

  所以，带一个值的 LIMIT 总是从第一行开始，给出的数为返回的行数。
  带两个值的 LIMIT 可以指定从行号为第一个值的位置开始。
  LIMIT 3, 4的含义是从行 4 开始的 3 行还是从行 3 开始的 4 行?
  如前所述，它的意思是从行 3 开始的 4 行（包括行 3），这容易把人搞糊涂。
  由于这个原因，MySQL 支持 LIMIT 的另一种替代语法：

1
LIMIT 4 OFFSET 3

  意为从行 3 开始取 4 行，就像 LIMIT 3, 4 一样

注意哦：LIMIT 第一条记录的序号为 0

数据处理函数

  SQL 支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便，函数包括：

  • 拼接函数：Concat()，如SELECT Concat(prod_name,'(',prod_price,')' FROM products
  • 聚集函数（常用）
  • 数值处理函数
  • 文本处理函数
  • 日期和时间处理函数

  具体说明见以下各类表格。

聚集函数

聚集函数 返回
AVG() 某列的平均值
COUNT() 某列的行数
MAX() 某列的最大值
MIN() 某列的最小值
SUM() 某列值之和

数值处理函数

数值处理函数 返回
Abs() 一个数的绝对值
Cos() 一个角度的余弦
Sin() 一个角度的正弦
Sqrt() 一个数的平方根
Tan() 一个角度的正切
Exp() 一个数的指数值
Mod() 除操作的余数
Pi() 圆周率
Rand() 一个随机数

文本处理函数

文本处理函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的 SOUNDEX 值
SubString() 返回子串的字符
Upper() 将串转换为大写

日期和时间处理函数

日期和时间处理函数 说明
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
DayOfWeek() 对于一个日期，返回对应的星期几
Year() 返回一个日期的年份部分
Month() 返回一个日期的月份部分
Day() 返回一个日期的天数部分
Time() 返回一个日期时间的时间部分
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Second() 返回一个时间的秒部分
Now() 返回当前日期和时间

Date_Format

  DATE_FORMAT()函数用于以不同的格式显示日期/时间数据。

  常用格式见下表：

格式 描述
%c 月，数值 1, 2, 3…12
%d 月的天，数值 00-31
%e 月的天，数值 0-31
%H 24 小时（00-23）
%h 12 小时（01-12）
%i 分钟，数值 00-59
%j 年的天 001-366
%k 24 小时 0-23
%l 12 小时 1-12
%m 月，数值 00-12
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒（00-59）
%s 秒（00-59）
%T 时间, 24-小时（hh:mm:ss）
%U 周（00-53）星期日是一周的第一天
%u 周（00-53）星期一是一周的第一天
%w 周的天（0=星期日, 6=星期六）
%Y 年，4 位

  在项目中的用法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 天每时，是否补字符串可和前端约定
SELECT DATE_FORMAT(createTime,'%H:00') AS hour, SUM(total_count) AS cnt
FROM company_project
WHERE createTime <![CDATA[>= ]]> #{beginTime}
  AND createTime <![CDATA[<= ]]> #{endTime}
GROUP BY hour

-- 星期每天，0=星期日, 6=星期六 注意在代码中将 0 替换为 7
SELECT DATE_FORMAT(createTime,'%w') AS day, SUM(total_count) AS cnt
FROM company_project
WHERE createTime <![CDATA[>= ]]> #{beginTime}
  AND createTime <![CDATA[<= ]]> #{endTime}
GROUP BY day

-- 月的天，数值（0-31）
SELECT DATE_FORMAT(createTime,'%e') AS day, SUM(total_count) AS cnt
FROM company_project
WHERE createTime <![CDATA[>= ]]> #{beginTime}
  AND createTime <![CDATA[<= ]]> #{endTime}
GROUP BY day

-- 年每月
SELECT DATE_FORMAT(createTime,'%c') AS month, SUM(total_count) AS cnt
FROM company_project
WHERE createTime <![CDATA[>= ]]> #{beginTime}
  AND createTime <![CDATA[<= ]]> #{endTime}
GROUP BY month

SELECT 语法顺序

  在 SELECT 语句中结合其他子句使用时必须遵循的次序如下表：

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

SELECT 语句运行顺序

  SELECT 语句运行时按以下子句进行：

  • ① FROM
  • ② ON
  • ③ JOIN（OUTER、INNER、LEFT、RIGHT）
  • ④ WHERE
  • ⑤ GROUP BY
  • ⑥ AVG、SUM 等聚合函数
  • ⑦ HAVING
  • ⑧ SELECT
  • ⑨ DISTINCT
  • ⑩ ORDER BY
  • ⑪ LIMIT

  从上述顺序我们不难发现，所有的查询语句都是从 FROM 开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入表。

  • 第一步：对 FROM 子句中的两个进行笛卡尔乘积，生成虚拟表 vt1
  • 第二步：应用 ON 筛选器，筛选出满足 ON 指定条件的行，生成虚拟表 vt2
  • 第三步：若为 OUTER JOIN 那么这一步则将添加外部行，若为 LEFT OUTER JOIN 则将左表在第二步中过滤的添加进来，若为 RIGHT OUTER JOIN 则将右表在第二步中过滤掉的行添加进来，之后生成虚拟表 vt3
  • 第四步：如果 FROM 子句中的表数目多余两个表，那么就将 vt3 和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3 的步骤，最终得到一个新的虚拟表 vt3
  • 第五步：应用 WHERE 筛选器，生成虚拟表 vt4
  • 第六步：将 GROUP BY 子句中的唯一的值组合成为一组，得到虚拟表 vt5。如果应用了 GROUP BY，那么后面的所有步骤都只能得到的 vt5 的 GROUP BY 列或者是聚合函数，原因在于最终的结果集中只为每个组包含一行
  • 第七步：执行聚合函数，应用 CUBE 或者 ROLLUP 选项，为 vt5 生成超组，生成 vt6，其中：
    • CUBE 生成的结果数据集显示了所选列中值的所有组合的聚合
    • ROLLUP 生成的结果数据集显示了所选列中值的某一层次结构的聚合
  • 第八步：应用 HAVING 筛选器，生成 vt7
  • 第九步：处理 SELECT 子句。将 vt7 表在 SELECT 中出现的列筛选出来，生成 vt8
  • 第十步：应用 DISTINCT 子句进行行去重，vt8 中移除相同的行，生成 vt9
  • 第十一步：应用 ORDER BY 子句排序 vt9，生成游标 vc10 ，注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时，ORDER BY 子句的执行顺序为从左到右排序，是非常消耗资源的。
  • 第十二步：应用 LIMIT 子句限制结果，从 vc10 的开始处选择指定数量行（若没有 LIMIT 子句则选择所有数据行），生成虚拟表 vt10，并返回调用者

扩展——去重

  当我们查询出想要的数据后，可能会遇到重复的数据，这个时候就需要去掉重复的数据，只显示一条。

  在 MySQL 中，若想进行去重操作，存在三种方式：

  • UNION：UNION会从查询结果集中自动去除重复行
  • DISTINCT：在 SELECT 中使用DISTINCT可对查询结果集的整行数据进行去重
  • GROUP BY：分组本身就是一种去重，需要注意的是严格模式下使用GROUP BY [约束字段]才能SELECT *，否则报错

数据操作语言 DML

  数据操作语言可以操作数据表中的数据，包括 3 种语句：

  • INSERT
  • UPDATE
  • DELETE

INSERT 插入数据

  在 MySQL 中，可以通过INSERT语句录入数据。

插入方式

  在 MySQL 中，通过INSERT插入数据的方式存在以下五种：

  • 插入完整的行
  • 插入部分行
  • 插入多个行
  • 插入检索出的数据
  • 成功的插入INSERT IGNORE
  • 更新的插入ON DUPLICATE KEY UPDATE

插入完整的行

  把数据插入表中的最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行中的值：

1
INSERT INTO 表名 VALUES(值，值，值...);

  存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值，应该使用NULL值（假定表允许对该列指定空值）。
  各个列必须以它们在表定义中出现的次序填充。通常第一列为自增的主键id，可以设置为NULL，之后每次插入一个新行时，该列由MySQL自动增量。

插入部分行

  若表的定义允许，则可在INSERT操作中省略某些列，其语法如下：

1
INSERT INTO 表名(列名，列名，列名...) VALUES(值，值，值...);

  但是需要注意的是，这些列必须满足以下某个条件：

  • 该列定义为允许 NULL 值（无值或空值）
  • 在表定义中给出了默认值，因此若不给出值，将使用默认值

插入多个行

  INSERT可以插入一行数据到一个表中，但如果你想插入多个行怎么办？
  使用多条INSERT语句，分次提交它们？

1
2
3
4
INSERT INTO 表名(列名，列名，列名...) VALUES(1A值，1B值，1C值...);
INSERT INTO 表名(列名，列名，列名...) VALUES(2A值，2B值，2C值...);
INSERT INTO 表名(列名，列名，列名...) VALUES(3A值，3B值，3C值...);
......

  上面的做法自然是一种可行方式，但对于 MySQL 而言，一次数据的插入就是一次连接，多次连接网络耗时在在大数据量情况下不容小觑！

  幸运的是，若想一次批量插入多条数据，可以使用另一种更优的语法：

1
INSERT INTO 表名(列名，列名，列名...) VALUES (1A值，1B值，1C值...), (2A值，2B值，2C值...), (3A值，3B值，3C值...), ......;

  当然，我们需要注意一次不要提交太多数据了，因此 MySQL 对于提交的数据是有大小限制的，超过了最大值就不允许执行了。

插入检索出的数据

  INSERT一般用来给表插入一个指定列值的行。但是，INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中，这就是所谓的INSERT SELECT。
  顾名思义，INSERT SELECT由一条INSERT语句和一条SELECT语句组成。

1
2
INSERT INTO A表(A列，B列，C列) 
SELECT A列, B列, C列 FROM B表 WHERE symbol = 'xxx';

  使用上面的语句，就可以将 B 表中的部分数据，非常轻松插入到 A表中。
  需要注意的是，SELECT B 表的列名必须和 A 表保持一致，当然，自增列和默认值列可以给 NULL 值：

1
2
INSERT INTO A表(id，B列，default_colum) 
SELECT NULL, B列, NULL FROM B表 WHERE symbol = 'xxx';

成功的插入 INSERT IGNORE

  为了适配业务需求做数据幂等控制，开发者经常会为表中的字段建立唯一索引。此时，若数据已存在于表中，在数据被唯一索引约束的情况下，数据二次插入将报错，无法成功插入了。

  能不能让INSERT根据情况判断：数据已存在则插入，数据不存在则不插入但语句显示执行成功。

  INSERT语句提供了额外的ignore方式的插入来达到这种效果。

1
INSERT IGNORE INTO `sys_user`(`id`, `username`) VALUES (123, 'leeqingshui');

  上述数据若不存在，则插入成功，存在，也显示插入成功，但返回影响的行数为 0.

更新的插入 ON DUPLICATE KEY UPDATE

  三方对接时，部分接口需要做幂等处理，幂等处理不仅要求客户调用接口多少次，均返回相同的结果，还要求数据做防重处理（申请接口调用多次，服务端只生成一条申请数据）。
  某些开发为了减少对接工作，可能会一个接口多用，比如说申请接口，即能申请数据，也能更新数据。（当然，数据还是得幂等不能重复哦！）

  申请接口自然是新增数据，对数据库而言就是INSERT操作了，但普通的INSERT操作都没法做到让第二次请求也更新数据，一般会判断已经存在就直接返回或已存在则报错（这取决于你使用的INSERT语句）。
  为了兼容这种情况，可以为INSERT语句配合使用ON DUPLICATE KEY UPDATE指定更新的字段，其会在插入数据之前进行判断：

  • 若主键或唯一索引不存在，则执行插入操作
  • 若主键或唯一索引已存在，则执行更新操作

  举个例子，sys_user表已存在id = 123, username = leeqingshui, age = 12的数据，此时执行以下 SQL 将更新age值 ：

1
INSERT INTO `sys_user`(`id`, `username`, `age`) VALUES (123, 'leeqingshui', 18) ON DUPLICATE KEY UPDATE age = 18;

注意哦：在高并发的场景下使用ON DUPLICATE KEY UPDATE语法，可能会出现死锁。

UPDATE 更新数据

  为了更新（修改）表中的数据，可使用UPDATE语句，有两种方式:

  • 更新表中所有行：UPDATE 表名 SET 列名 = 新值
  • 更新表中特定行：UPDATE 表名 SET 列名 = 新值 WHERE ...

  为了清空某个列的值，可设置它为 NULL （假如表定义允许 NULL 值）。
  比如UPDATE 表名 SET 列名 = NULL

DELETE 删除数据

  为了从一个表中删除（去掉）数据，可使用DELETE语句，有两种方式:

  • 从表中删除所有行：DELETE FROM 表名
  • 从表中删除特定的行：DELETE FROM 表名 WHERE ...

注意哦：虽然DELETE语句会从表中删除行，甚至是删除表中所有行，但是，DELETE并不删除表本身。若想从表中删除所有行，可以使用TRUNCATE TABLE语句

数据控制语言 DCL

  事务相关一般不会直接通过 SQL 语句来控制，而是通过程序间接处理。此处略过。

扩展

建库

1
2
-- 建库
CREATE DATABASE IF NOT EXISTS bifrost DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

查看表字段

  通过DESC TABLE_NAME命令可以查看表的字段设置情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> DESC `trace_access_log`;
+-----------------+------------------+------+-----+-------------------+-------------------+
| Field           | Type             | Null | Key | Default           | Extra             |
+-----------------+------------------+------+-----+-------------------+-------------------+
| id              | bigint unsigned  | NO   | PRI | NULL              | auto_increment    |
| username        | varchar(30)      | NO   |     | NULL              |                   |
| browser         | varchar(50)      | NO   |     | NULL              |                   |
| os              | varchar(50)      | NO   |     | NULL              |                   |
| access_ip       | varchar(128)     | NO   |     | NULL              |                   |
| access_location | varchar(255)     | NO   |     | NULL              |                   |
| access_type     | tinyint unsigned | NO   |     | 0                 |                   |
| is_normal       | tinyint(1)       | NO   |     | 1                 |                   |
| status_info     | varchar(255)     | NO   |     | 登陆成功          |                   |
| create_time     | datetime         | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------------+------------------+------+-----+-------------------+-------------------+

查看表索引

  通过SHOW INDEX FROM TABLE_NAME命令可以查看表的索引设置情况：

1
2
3
4
5
6
7
8
mysql> SHOW INDEX FROM `trace_access_log`;
+------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table            | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| trace_access_log |          0 | PRIMARY                  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| trace_access_log |          1 | idx_create_time_username |            1 | create_time | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| trace_access_log |          1 | idx_create_time_username |            2 | username    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

查看建表的语句

  如果我既想看表的字段情况，又想看表的索引情况，能否通过一行命令查看建表语句呢？

  当然可以，通过SHOW CREATE TABLE TABLE_NAME命令就行：

1
SHOW CREATE TABLE `trace_access_log`;

表快速备份

  有时候，我们需要快速备份表，这当然可以使用一些工具帮助我们实现（如 Navicat）。

  但若工具无法使用，只能通过命令的方式去做这件事，该如何操作呢？

  表的备份通常需要分两步：

  • ① 创建一张临时表
  • ② 将数据插入临时表

  创建临时表可以使用命令：

1
CREATE TABLE `trace_access_log_back_1` LIKE `trace_access_log`;

  创建成功之后，就会生成一张名为trace_access_log_back_1且表结构跟trace_access_log相同的新表，只是该表的数据为空。

  为了往空表塞入数据，使用我们之前学的INSERT ... SELECT语句即可：

1
INSERT INTO `trace_access_log_back_1` SELECT * FROM `trace_access_log`;

  上述语句执行之后，指定表的数据就会备份到新表啦！

  那有没有更好的命令，通过一个命令就实现之前两步的功能呢？

  使用CREATE TABLE ... SELECT命令即可。

  举个例子：

1
2
CREATE TABLE trace_access_log_back_2 
SELECT * FROM `trace_access_log`;

  以上语句，也能达到相同的效果，一个命令搞定表的备份。

查看数据占用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查看各个数据库的数据占用情况
SELECT
        table_schema AS '数据库',
        SUM(table_rows) AS '记录数',
        SUM(TRUNCATE(data_length/1024/1024/1024, 2)) AS '数据容量-GB',
        SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量-MB',
        SUM(TRUNCATE(DATA_FREE/1024/1024, 2)) AS '碎片占用-MB'
FROM information_schema.tables
GROUP BY table_schema
order by SUM(data_length) DESC, SUM(index_length) DESC;

-- 查看指定数据库（指定表）的数据占用情况
SELECT
        table_schema AS '数据库',
        table_name AS '表名',
        table_rows AS '记录数',
        TRUNCATE(data_length/1024/1024, 2) AS '数据容量-MB',
        TRUNCATE(index_length/1024/1024, 2) AS '索引容量-MB'
FROM information_schema.tables
WHERE table_schema = '库名'
AND table_name LIKE '表名'
ORDER BY table_rows DESC;

连表更新

1
2
3
4
5
-- 联表 UPDATE
UPDATE sys_user AS u, complex_company AS p
SET p.departmentId = u.departmentId
WHERE u.id = p.createUserId
AND u.realName IN ('');

参考

  • Ben Forta. MySQL 必知必会 [M]. 人民邮电出版社, 2009

文章信息

时间 说明
2019-02-08 初稿
2022-08-20 微调
2022-12-21 新增扩展小节
0%